﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using HenIT.Data.SqlClient;

namespace EventScavenger
{
    public delegate void RaiseMessageDelegate(string message);

    public partial class EventScavengerDAL : GenericSQLServerDAL
    {
        private SqlConnection insertConn;
        private System.Threading.Mutex insertMutex = new System.Threading.Mutex();
        private System.Threading.Mutex connResetMutex = new System.Threading.Mutex();

        #region Events
        public event RaiseMessageDelegate RaiseInfoMessage;
        private void DoRaiseInfoMessage(string message)
        {
            if (RaiseInfoMessage != null)
            {
                RaiseInfoMessage(message);
            }
        }
        public event RaiseMessageDelegate RaiseErrorMessage;
        private void DoRaiseErrorMessage(string message)
        {
            if (RaiseErrorMessage != null)
            {
                RaiseErrorMessage(message);
            }
        }
        public event RaiseMessageDelegate RaiseSyncInfoMessage;
        private void DoRaiseSyncInfoMessage(string message)
        {
            if (RaiseSyncInfoMessage != null)
            {
                RaiseSyncInfoMessage(message);
            }
        }
        #endregion

        #region Import logs
        public List<ImportForeignLog> SelectImportLogsByCollector(int collectorId)
        {
            string sql = "SelectImportLogsByCollector";
            List<ImportForeignLog> list = new List<ImportForeignLog>();
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@CollectorID", collectorId)
                };
            try
            {
                DataSet importList = base.GetDataSet(sql, CommandType.StoredProcedure, parms);
                if (importList.Tables.Count > 0)
                {
                    list.AddRange(from DataRow row in importList.Tables[0].Rows
                                  select new ImportForeignLog()
                                  {
                                      SQLServer = row["Server"].ToString(),
                                      Database = row["Database"].ToString(),
                                      Machine = row["Machine"].ToString(),
                                      LogName = row["LogName"].ToString()
                                  }
                                 );
                }
            }
            catch (Exception ex)
            {
                lastError = ex.ToString();
            }

            return list;
        }
        public void ImportLog(string dbServer, string database, List<MachineLogEntry> logs)
        {
            string sourceConnectionString = "Server=" + dbServer + ";Database=" + database + ";Trusted_Connection=True;";
            DateTime fromDate; DateTime toDate = DateTime.Now;
            int recordsCopied;
            int duplicates;
            string lastStep = "ImportLog start";
            try
            {
                using (SqlConnection connSource = new SqlConnection(sourceConnectionString))
                {
                    lastStep = "Open source connection";
                    connSource.Open();

                    foreach (MachineLogEntry log in logs)
                    {
                        lastStep = "Check machine-log exists on destination";
                        MachineLogEntry updatedLog = GetMachineLogEntry(log.Machine, log.LogName);
                        if (updatedLog.MachineLogID > 0)
                        {
                            lastStep = "Get machine-log newest entry date";
                            fromDate = GetMachineLogNewestEntry(updatedLog);
                            lastStep = "Get entries from distant source";
                            List<EventEntry> entryList = GetRemoteEntries(connSource, log, fromDate, toDate);
                            recordsCopied = 0;
                            duplicates = 0;
                            lastStep = "For each source insert into destination database";
                            foreach (EventEntry eventEntry in entryList)
                            {
                                try
                                {
                                    lastStep = "Add entry";
                                    InsertEventLogEntry(eventEntry);
                                    recordsCopied++;
                                }
                                catch (Exception ex)
                                {
                                    lastStep = "Logging error - " + ex.Message;
                                    if (
                                        ex.Message.Contains("Violation of PRIMARY KEY constraint") ||
                                        ex.Message.Contains("Cannot insert duplicate key")
                                        )
                                    {
                                        duplicates++;
                                    }
                                    else
                                    {
                                        DoRaiseErrorMessage("Error adding: " + eventEntry.ToString() + "   \r\n" + ex.ToString());
                                    }
                                }
                            }

                            DoRaiseSyncInfoMessage("Import done: " + dbServer + "\\" + database + "\\" + log.Machine.ToString() + "\\" + log.LogName.ToString() + "\r\n" +
                                        "Records copied: " + recordsCopied.ToString() + "\r\n" +
                                        "Duplicates ignored: " + duplicates.ToString());
                        }
                        else
                        {
                            DoRaiseErrorMessage("Import failed! The destination log entry does not exist!\r\nSource:" + dbServer + "\\" + database + "\\" + log.Machine.ToString());
                        }
                        
                    }
                }
                lastStep = "Import done";
            }
            catch (Exception ex)
            {
                throw new Exception("Last Step: " + lastStep, ex);
            }
        }
        #endregion

        #region MachineLog methods
        public List<MachineLogEntry> GetMachineLogListByCollector(string collectorName)
        {
            List<MachineLogEntry> list = new List<MachineLogEntry>();
            string sql = "SelectMachineLogListByCollector";
            lastError = "";
            try
            {
                DataSet machineList = base.GetDataSet(sql, CommandType.StoredProcedure, new SqlParameter[] { new SqlParameter("@Collector", collectorName) });
                if (machineList.Tables.Count > 0)
                {
                    list.AddRange(from DataRow row in machineList.Tables[0].Rows
                                  select new MachineLogEntry(
                                  row["Machine"].ToString(),
                                  row["LogName"].ToString(),
                                  (int)row["PollFrequency"])
                                  {
                                      Collector = row["Collector"].ToString(),
                                      CollectorId = (int)row["CollectorID"]
                                  }
                                 );
                }
            }
            catch (Exception ex)
            {
                lastError = ex.ToString();
            }
            return list;
        }
        private void InsertNewMLE(MachineLogEntry importedMle)
        {
            MachineLogEntry localMle = new MachineLogEntry(importedMle.Machine, importedMle.LogName);
            string sql = "InsertMachineLog";
            SqlParameter[] parameters = new SqlParameter[] 
            {
                new SqlParameter("@Machine", importedMle.Machine),
                new SqlParameter("@LogName", importedMle.LogName)
            };
            base.ExecuteSP(sql, parameters);
            DoRaiseDBAccessInsertsEvent();
        }
        public void SetLastMachineLogPollingTime(string machine, string logName, DateTime lastPollTime)
        {
            string sql = "SetLastPollTimeForMachineLog";
            try
            {
                //sqlAccessBusy.WaitOne();
                SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@Machine", machine),
                    new SqlParameter("@LogName", logName),
                    new SqlParameter("@LastPollTime", lastPollTime.AddSeconds(-3)) //Allow grace period of 3 seconds to avoid events being missed
                };
                base.ExecuteSP(sql, parms);
            }
            catch { throw; }
            finally
            {
                //sqlAccessBusy.ReleaseMutex();
            }
        }
        private DateTime GetMachineLogNewestEntry(MachineLogEntry mle)
        {
            DateTime lastEntryDate = new DateTime(2000, 1, 1);
            string sql = "GetMachineLogMaxTimeGenerated";
            SqlParameter[] parameters = new SqlParameter[] 
            {
                new SqlParameter("@MachineLogID",mle.MachineLogID)
            };
            object t = base.GetSingleValue(sql, CommandType.StoredProcedure, parameters);
            if (t != DBNull.Value)
            {
                lastEntryDate = ((DateTime)t).AddSeconds(-1); //make sure we get all possible entries
            }
            return lastEntryDate;
        }
        #endregion

        #region Test Connection
        public bool TestConnection()
        {
            bool success = false;
            string sql = "select getdate()";
            try
            {
                //sqlAccessBusy.WaitOne();
                ExecuteSQL(sql);
                success = true;
            }
            catch (Exception ex)
            {
                lastError = ex.ToString();
            }
            finally
            {
                //sqlAccessBusy.ReleaseMutex();
            }
            return success;
        }
        #endregion

        #region InsertEventLogEntry
        private void InsertEventLogEntry(EventEntry eventEntry)
        {
            InsertEventLogEntry(
                eventEntry.MachineName,
                eventEntry.LogName,
                eventEntry.RecordNumber,
                eventEntry.EventID,
                eventEntry.TimeGenerated,
                eventEntry.SourceName,
                eventEntry.EventTypeName,
                "",
                eventEntry.Message,
                eventEntry.Category,
                eventEntry.UserName);
        }
        public void InsertEventLogEntry(
            string computerName,
            string eventLogName,
            int recordNumber,
            int eventId,
            DateTime timeGenerated,
            string sourceName,
            string eventTypeName,
            string strings,
            string message,
            string category,
            string userName
        )
        {
            try
            {
                connResetMutex.WaitOne();                
                if (insertConn == null || insertConn.State != ConnectionState.Open)
                {
#if DEBUG
                    System.Diagnostics.Trace.WriteLine(
                        string.Format("Resetting connection - {0}\\{1}\\{2}\\{3}\\{4}",
                        computerName, eventLogName, sourceName, eventId, eventTypeName));
#endif
                    insertConn = null;
                    insertConn = new SqlConnection(connectionString);
                    insertConn.Open();
                }
            }
            catch { throw; }
            finally
            {
                connResetMutex.ReleaseMutex();
            }
            try
            {
                string sql = "InsertEventLogEntry";
                SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@ComputerName", computerName),
                    new SqlParameter("@EventLog", eventLogName),
                    new SqlParameter("@RecordNumber", recordNumber),
                    new SqlParameter("@EventID", eventId),
                    new SqlParameter("@TimeGenerated", timeGenerated),
                    new SqlParameter("@SourceName", sourceName),
                    new SqlParameter("@EventTypeName", eventTypeName.Substring(0,1).ToUpper()),
                    new SqlParameter("@Strings", strings),
                    new SqlParameter("@Message", message),
                    new SqlParameter("@Category", category),
                    new SqlParameter("@UserName", userName)
                };
                using (SqlCommand cmnd = new SqlCommand(sql, insertConn))
                {
                    cmnd.CommandType = CommandType.StoredProcedure;
                    cmnd.CommandTimeout = CommandTimeout;
                    cmnd.Parameters.AddRange(parms);
                    try
                    {
                        insertMutex.WaitOne(); //make sure only one insert is done at a time
                        cmnd.ExecuteNonQuery();                        
                    }
                    catch { throw; }
                    finally
                    {
                        insertMutex.ReleaseMutex();
                    }
                }
                DoRaiseDBAccessInsertsEvent();
            }
            catch (System.Data.SqlClient.SqlException innerseex)
            {
                //Ignoring duplicates - they are in the database already...
                if (innerseex.Message.Contains("Violation of PRIMARY KEY constraint") ||
                    innerseex.Message.Contains("Cannot insert duplicate key"))
                    DoRaiseDBAccessInsertDuplicates();
                else
                {
                    throw;
                }
            }
            catch //rethrow the rest
            {
                throw;
            }
        }
        public string InsertEventLogEntries(System.IO.Stream xml)
        {
            string infoResult = "";
            #region Connection reset
            try
            {
                connResetMutex.WaitOne();
                if (insertConn == null || insertConn.State != ConnectionState.Open)
                {
                    insertConn = null;
                    insertConn = new SqlConnection(connectionString);
                    insertConn.Open();
                }
            }
            catch { throw; }
            finally
            {
                connResetMutex.ReleaseMutex();
            } 
            #endregion

            string sql = "InsertEventLogEntries";
            System.Data.SqlTypes.SqlXml sxl = new System.Data.SqlTypes.SqlXml(xml);
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@xml", SqlDbType.Xml) { Value = sxl}
                };
            using (SqlCommand cmnd = new SqlCommand(sql, insertConn))
            {
                cmnd.CommandType = CommandType.StoredProcedure;
                cmnd.CommandTimeout = CommandTimeout;
                cmnd.Parameters.AddRange(parms);
                try
                {
                    insertMutex.WaitOne(); //make sure only one insert is done at a time
                    using (SqlDataReader r = cmnd.ExecuteReader())
                    {
                        if (r.Read())
                        {
                            int duplicates = (int)r["Duplicates"];
                            int repeats = (int)r["Repeats"];
                            if (duplicates > 1 || repeats > 0)
                            {
                                infoResult = string.Format("{0} inserts, {1} duplicates, {2} repeats, {3} failures, " +
                            "{4}ms duration, last err: {5}",
                                r["Inserts"],
                                r["Duplicates"],
                                r["Repeats"],
                                r["InsertFailures"],
                                r["MSDuration"],
                                r["LastErrorMsg"]);
                            }                            
                        }
                    }
                    DoRaiseDBAccessInsertsEvent();
                }
                catch { throw; }
                finally
                {
                    insertMutex.ReleaseMutex();
                }
            }
            return infoResult;
        }
        #endregion

        #region Maintenance
        public void RunMaintenance(int commandTimeOut)
        {
            string sql = "DoMaintenance";
            base.ExecuteSP(sql, null, commandTimeOut);
        }
        public bool IsMaintenanceEnabled()
        {
            bool isEnabled = false;
            string sql = "GetSettingValue";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@SettingName", "MaintenanceEnabled")
                };
            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                isEnabled = bool.Parse(tmpVal.ToString());
            return isEnabled;
        }
        public string GetMaintenanceRunner()
        {
            string maintenanceRunner = "";
            string sql = "GetSettingValue";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@SettingName", "MaintenanceRunner")
                };
            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                maintenanceRunner = tmpVal.ToString();
            return maintenanceRunner;
        }
        public int GetMaintenanceFrequencyMinutes()
        {
            int maintenanceFrequencyMinutes = 5;
            string sql = "GetSettingValue";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@SettingName", "MaintenanceFrequencyMinutes")
                };
            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                maintenanceFrequencyMinutes = int.Parse(tmpVal.ToString());
            return maintenanceFrequencyMinutes;
        }
        public int GetMaintenanceTimeOutSeconds()
        {
            int maintenanceTimeOutSeconds = 300;
            string sql = "GetSettingValue";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@SettingName", "MaintenanceTimeOutSeconds")
                };
            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                maintenanceTimeOutSeconds = int.Parse(tmpVal.ToString());
            return maintenanceTimeOutSeconds;
        }
        public void SetMaintenanceLastRunTime()
        {
            string sql = "SetMaintenanceLastRunTime";
            base.ExecuteSP(sql);
        }
        #endregion

        #region RecycleTime
        public DateTime GetNextRecycleTime(string collectorName)
        {
            DateTime nextRecycleTime = new DateTime(2000, 1, 1);
            string sql = "GetCollectorNextRecycleTime";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@CollectorName", collectorName)
                };

            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                nextRecycleTime = (DateTime)tmpVal;

            return nextRecycleTime;
        }
        public bool GetThreadRecyclingEnabled(string collectorName)
        {
            bool isEnabled = false;
            string sql = "GetCollectorThreadRecyclingEnabled";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@CollectorName", collectorName)
                };

            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                isEnabled = (bool)tmpVal;

            return isEnabled;
        }
        public void SetNextRecycleTime(string collectorName)
        {
            string sql = "SetCollectorNextRecycleTime";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@CollectorName", collectorName)
                };
            base.ExecuteSP(sql, parms);
        }
        public int GetRecyclingCheckFrequencyMinutes()
        {
            int recyclingCheckFrequencyMinutes = 5;
            string sql = "GetSettingValue";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@SettingName", "RecyclingCheckFrequencyMinutes")
                };
            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                recyclingCheckFrequencyMinutes = int.Parse(tmpVal.ToString());
            return recyclingCheckFrequencyMinutes;
        }
        #endregion

        #region Create history
        public bool GetRunCreateHistoryEnabled()
        {
            string sql = "GetSettingValue";
            bool maintenanceEnabled = false;
            SqlParameter[] parameters = new SqlParameter[] 
                {
                    new SqlParameter("@SettingName", "RunCreateHistoryEnabled")
                };
            object tmpval = GetSingleValue(sql, CommandType.StoredProcedure, parameters);
            if (tmpval != DBNull.Value)
                maintenanceEnabled = bool.Parse(tmpval.ToString());
            return maintenanceEnabled;
        }
        public void SetNextCreateHistoryTime()
        {
            string sql = "SetNextCreateHistoryTime";
            base.ExecuteSP(sql);
        }
        public DateTime GetNextCreateHistoryTime()
        {
            DateTime nextCreateHistoryTime = new DateTime(2000, 1, 1);
            string sql = "GetSettingValue";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@SettingName", "NextCreateHistoryTime")
                };
            object tmpVal = base.GetSingleValue(sql, CommandType.StoredProcedure, parms);
            if (tmpVal != DBNull.Value)
                nextCreateHistoryTime = DateTime.Parse(tmpVal.ToString());

            return nextCreateHistoryTime;
        }
        public void RunCreateHistory()
        {
            string sql = "DoCreateHistory";
            SqlParameter[] parms = new SqlParameter[] { };
            base.ExecuteSP(sql, parms, 3600); // 1 hour
        }
        #endregion

        #region Remote entries
        private List<EventEntry> GetRemoteEntries(SqlConnection connSource, MachineLogEntry mle, DateTime fromDate, DateTime toDate)
        {
            List<EventEntry> list = new List<EventEntry>();

            string sql = "SelectEventLogEntriesByMachineLogName";
            SqlParameter[] parameters = new SqlParameter[] 
            {
                new SqlParameter("@FromDate", fromDate),
                new SqlParameter("@ToDate", toDate),
                new SqlParameter("@ComputerName", mle.Machine),
                new SqlParameter("@EventLog", mle.LogName)
            };

            DataSet dsEntries = new DataSet();
            DoRaiseDBAccessEvent();
            try
            {
                using (SqlCommand cmnd = new SqlCommand(sql, connSource))
                {
                    cmnd.CommandTimeout = CommandTimeout;
                    cmnd.CommandType = CommandType.StoredProcedure;
                    cmnd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmnd))
                    {
                        da.Fill(dsEntries);
                    }
                }
            }
            catch
            {
                DoRaiseDBAccessErrorsEvent();
                throw;
            }

            if (dsEntries.Tables.Count > 0)
            {
                list.AddRange(
                        from DataRow row in dsEntries.Tables[0].Rows
                        select new EventEntry()
                        {
                            MachineName = mle.Machine,
                            LogName = mle.LogName,
                            RecordNumber = int.Parse(row["RecordNumber"].ToString()),
                            EventID = int.Parse(row["EventID"].ToString()),
                            TimeGenerated = DateTime.Parse(row["TimeGenerated"].ToString()),
                            SourceName = row["SourceName"].ToString(),
                            EventTypeName = row["EventTypeName"].ToString(),
                            Message = row["Message"].ToString(),
                            Category = row["Category"] == DBNull.Value ? "" : row["Category"].ToString(),
                            UserName = row["UserName"] == DBNull.Value ? "" : row["UserName"].ToString()
                        }
                       );
            }
            return list;
        } 
        #endregion

        #region Collectors
        public Collector GetCollectorByName(string collectorName)
        {
            string sql = "GetCollectorByName";
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter("@CollectorName", collectorName)
                };
            
            Collector collector = null;
            try
            {
                DataSet collectords = base.GetDataSet(sql, CommandType.StoredProcedure, parms);
                if (collectords.Tables.Count > 0)
                {
                    collector = (from DataRow row in collectords.Tables[0].Rows
                                 select new Collector()
                                  {
                                      Id = (int)row["CollectorID"],
                                      Name = row["Name"].ToString(),
                                      ThreadRecycleFrequencyMinutes = (int)row["ThreadRecycleFrequencyMinutes"] ,
                                      NextRecycleTime = (DateTime)row["NextRecycleTime"],
                                      ThreadRecyclingEnabled = (bool)row["ThreadRecyclingEnabled"],
                                      ImportsEnabled = (bool)row["ImportsEnabled"],
                                      ImportFrequency = (int)row["ImportFrequencyMinutes"]
                                  }
                                 ).First();
                }
            }
            catch (Exception ex)
            {
                lastError = ex.ToString();
            }
            return collector;
        }
        
        #endregion
    }
}
